﻿using System;
using System.Collections.Generic;
using System.Data;
using CoreLab.Oracle;
using LMD.Lib.Oracle;
using LMD.Lib.DataClass;

namespace Controller
{
    /// <summary>
    /// Summary description for Survey Controller
    /// </summary>
    public class NPSSurveyController : ControllerBase
    {
        private void CreateNPSSurveyNewsFeed(DBLib dbl, Entities.NPSSurvey ciSurvey)
        {
            NewsFeedData nfd = new NewsFeedData();

            NPSCustomerContactController ncc = new NPSCustomerContactController();
            int customerID = ncc.getNPSCustomerContact(ciSurvey.iContactPersonID, false).iCompanyID;

            nfd.iCustomerId = customerID;
            nfd.iSourceId = (int)NewsFeedSourceType.ICP_NPS;
            nfd.sxUserId_Poster = ICPUser.ID;
            nfd.sxTitle = "Survey result has been added!";
            nfd.sxContent = "Survey : " + ciSurvey.iContactPersonID;
            CreateNewsFeed(dbl, nfd);
        }

        public int InsertNPSSurveyData(Entities.NPSSurvey ciSurvey)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_REASON_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSReasonID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CP_CATEGORY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSCPCategoryID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CONTACT_PERSON_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iContactPersonID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SCORE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iScore;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSTypeID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SURVEY_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = ciSurvey.dtmSurveyDate;


            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_I_NPS_SURVEY";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            CreateNPSSurveyNewsFeed(dbl, ciSurvey);
            return Convert.ToInt32(lsParams[0].Value);
        }

        public void UpdateNPSSurveyDataKategori(Entities.NPSSurvey ciSurvey)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_iID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_iNPSCPCategoryID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSCPCategoryID;

            String sxQuery =
                @"
                update 
                    NPS_SURVEY
                set
                    P_NPS_CP_CATEGORY_ID = :in_iNPSCPCategoryID
                where
                    NPS_SURVEY_ID = :in_iID
                ";

            dbl.ExecuteNonQuery(sxQuery, lsParams.ToArray());
        }
        
        public void UpdateNPSSurveyData(Entities.NPSSurvey ciSurvey)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_Survey_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SCORE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Integer;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iScore;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_REASON_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Integer;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSReasonID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CP_CATEGORY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Integer;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSCPCategoryID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iNPSTypeID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SURVEY_DATE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
            lsParams[lsParams.Count - 1].Value = ciSurvey.dtmSurveyDate;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CONTACT_PERSON_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Integer;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iContactPersonID;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATOR";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_U_NPS_SURVEY";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public void DeleteNPSSurveyData(Entities.NPSSurvey ciSurvey)
        {
            DBLib dbl = new DBLib();
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_SURVEY_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = ciSurvey.iID;

            String sxQuery = "PR_D_NPS_SURVEY";
            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public List<Entities.NPSSurvey> NPSSurveyList
        {
            get
            {
                return getNPSSurveyList(false);
            }
        }

        public List<Entities.NPSSurvey> DetailedNPSSurveyList
        {
            get
            {
                return getNPSSurveyList(true);
            }
        }

        public List<Entities.NPSSurvey> getNPSSurveyList(bool isDetailed)
        {
            DBLib dbl = new DBLib();

            String sxQuery = @"select 
                    NPS_SURVEY_ID,
	                P_NPS_REASON_ID,
	                P_NPS_CP_CATEGORY_ID,
	                P_CONTACT_PERSON_ID,
	                SURVEY_DATE,
	                SCORE,
            	    P_NPS_TYPE_ID,
	               CREATE_BY,
                    CREATE_DATE,
                    UPDATE_BY,
	                UPDATE_DATE,
                    VALID_FROM,
                    VALID_TO
                from 
                    NPS_SURVEY
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE
                ";

            DataTable dt = dbl.GetDataTable(sxQuery);
            NPSCPCategoryController CPController = new NPSCPCategoryController();
            NPSReasonController SRController = new NPSReasonController();
            List<Entities.NPSSurvey> SurveyList = new List<Entities.NPSSurvey>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSSurvey Survey = new Entities.NPSSurvey();
                Survey.iID = Convert.ToInt32(row["NPS_SURVEY_ID"].ToString());
                Survey.iContactPersonID = Convert.ToInt32(row["P_CONTACT_PERSON_ID"].ToString());
                Survey.iScore = Convert.ToInt32(row["SCORE"].ToString());
                Survey.iNPSCPCategoryID = Convert.ToInt32(row["P_NPS_CP_CATEGORY_ID"].ToString());
                Survey.iNPSReasonID = Convert.ToInt32(row["P_NPS_REASON_ID"].ToString());
                Survey.dtmSurveyDate = DateTime.Parse(row["SURVEY_DATE"].ToString());
                Survey.iNPSTypeID = Convert.ToInt32(row["P_NPS_TYPE_ID"].ToString());
                Survey.sxCreator = row["CREATE_BY"].ToString();
                Survey.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                Survey.sxCreator = row["UPDATE_BY"].ToString();
                Survey.dtmCreated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                if (isDetailed)
                {
                    Survey.oNPSCPCategory = CPController.getNPSCPCategory(Survey.iNPSCPCategoryID);
                    Survey.oNPSReason = SRController.getNPSReason(Survey.iNPSReasonID, true);
                }
                SurveyList.Add(Survey);
            }
            return SurveyList;

        }

        public List<Entities.NPSSurvey> getNPSSurveyList(int contactID)
        {
            DBLib dbl = new DBLib();

            String sxQuery = @"select 
                    NPS_SURVEY_ID,
	                P_NPS_REASON_ID,
	                P_NPS_CP_CATEGORY_ID,
	                P_CONTACT_PERSON_ID,
	                SURVEY_DATE,
	                SCORE,
            	    P_NPS_TYPE_ID,
	               CREATE_BY,
                    CREATE_DATE,
                    UPDATE_BY,
	                UPDATE_DATE,
                    VALID_FROM,
                    VALID_TO
                from 
                    NPS_SURVEY
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and p_contact_person_ID=:prmID
        order by survey_date desc
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = contactID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            NPSCPCategoryController CPController = new NPSCPCategoryController();
            NPSReasonController SRController = new NPSReasonController();
            List<Entities.NPSSurvey> SurveyList = new List<Entities.NPSSurvey>();
            foreach (DataRow row in dt.Rows)
            {
                Entities.NPSSurvey Survey = new Entities.NPSSurvey();
                Survey.iID = Convert.ToInt32(row["NPS_SURVEY_ID"].ToString());
                Survey.iContactPersonID = Convert.ToInt32(row["P_CONTACT_PERSON_ID"].ToString());
                Survey.iScore = Convert.ToInt32(row["SCORE"].ToString());
                Survey.iNPSCPCategoryID = Convert.ToInt32(row["P_NPS_CP_CATEGORY_ID"].ToString());
                Survey.iNPSReasonID = Convert.ToInt32(row["P_NPS_REASON_ID"].ToString());
                Survey.dtmSurveyDate = DateTime.Parse(row["SURVEY_DATE"].ToString());
                Survey.iNPSTypeID = Convert.ToInt32(row["P_NPS_TYPE_ID"].ToString());
                Survey.sxCreator = row["CREATE_BY"].ToString();
                Survey.dtmCreated = DateTime.Parse(row["CREATE_DATE"].ToString());
                Survey.sxCreator = row["UPDATE_BY"].ToString();
                Survey.dtmCreated = DateTime.Parse(row["UPDATE_DATE"].ToString());
                Survey.oNPSCPCategory = CPController.getNPSCPCategory(Survey.iNPSCPCategoryID);
                Survey.oNPSReason = SRController.getNPSReason(Survey.iNPSReasonID, true);

                SurveyList.Add(Survey);
            }
            return SurveyList;

        }



        public Entities.NPSSurvey getNPSSurvey(int SurveyID, bool isDetailed)
        {
            DBLib dbl = new DBLib();
            String sxQuery =
                @"select 
                   NPS_SURVEY_ID,
	                P_NPS_REASON_ID,
	                P_NPS_CP_CATEGORY_ID,
	                P_CONTACT_PERSON_ID,
	                SURVEY_DATE,
	                SCORE,
            	    P_NPS_TYPE_ID,
	                CREATE_BY,
                    CREATE_DATE,
                    UPDATE_BY,
	                UPDATE_DATE,
                    VALID_FROM
                    VALID_TO
                from 
                    NPS_SURVEY
                where 
                    NVL (VALID_FROM, SYSDATE + 1) < SYSDATE
        AND NVL (VALID_TO, SYSDATE + 1) > SYSDATE and
                    NPS_SURVEY_ID = :prmID
                ";
            List<OracleParameter> lsParams = new List<OracleParameter>();
            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "prmID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = SurveyID;
            DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
            Entities.NPSSurvey Survey = new Entities.NPSSurvey();
            try
            {
                Survey.iID = Convert.ToInt32(dt.Rows[0]["NPS_SURVEY_ID"].ToString());
                Survey.iContactPersonID = Convert.ToInt32(dt.Rows[0]["P_CONTACT_PERSON_ID"].ToString());
                Survey.iScore = Convert.ToInt32(dt.Rows[0]["SCORE"].ToString());
                Survey.iNPSCPCategoryID = Convert.ToInt32(dt.Rows[0]["P_NPS_CP_CATEGORY_ID"].ToString());
                Survey.iNPSReasonID = Convert.ToInt32(dt.Rows[0]["P_NPS_REASON_ID"].ToString());
                Survey.dtmSurveyDate = DateTime.Parse(dt.Rows[0]["SURVEY_DATE"].ToString());
                Survey.iNPSTypeID = Convert.ToInt32(dt.Rows[0]["P_NPS_TYPE_ID"].ToString());
                Survey.sxCreator = dt.Rows[0]["CREATE_BY"].ToString();
                Survey.dtmCreated = DateTime.Parse(dt.Rows[0]["CREATE_DATE"].ToString());
                Survey.sxCreator = dt.Rows[0]["UPDATE_BY"].ToString();
                Survey.dtmCreated = DateTime.Parse(dt.Rows[0]["UPDATE_DATE"].ToString());
                if (isDetailed)
                {
                    NPSCPCategoryController CPController = new NPSCPCategoryController();
                    Survey.oNPSCPCategory = CPController.getNPSCPCategory(Survey.iNPSCPCategoryID);
                    NPSReasonController SRController = new NPSReasonController();
                    Survey.oNPSReason = SRController.getNPSReason(Survey.iNPSReasonID, true);
                    NPSTypeController TController = new NPSTypeController();
                    Survey.oNPSType = TController.getNPSType(Survey.iNPSTypeID);
                }
            }
            catch (Exception p) { errLog = p.ToString(); }
            return Survey;
        }
    }
}